﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;

    public class InvSkuLocDA : IInvSkuLocDA
    {
        private string ALLOCATED_QTY = "@ALLOCATED_QTY";
        private string ALLOW_REPLENISH_FROM_BULK = "@ALLOW_REPLENISH_FROM_BULK";
        private string ALLOW_REPLENISH_FROM_CASEPICK = "@ALLOW_REPLENISH_FROM_CASEPICK";
        private string ALLOW_REPLENISH_FROM_PIECEPICK = "@ALLOW_REPLENISH_FROM_PIECEPICK";
        private string CREATED_BY = "@CREATED_BY";
        private string EXPECTED_QTY = "@EXPECTED_QTY";
        private string LOC = "@LOC";
        private string LOCATION_TYPE = "@LOCATION_TYPE";
        private string LOCATION_UOM = "@LOCATION_UOM";
        private string OPT_ADD_DEL_FLAG = "@OPT_ADD_DEL_FLAG";
        private string OPT_BATCHID = "@OPT_BATCHID";
        private string OPT_SEQUENCE = "@OPT_SEQUENCE";
        private string OWNER_ID = "@OWNER_ID";
        private string PICKED_QTY = "@PICKED_QTY";
        private string QTY = "@QTY";
        private string QTY_LOCATION_LIMIT = "@QTY_LOCATION_LIMIT";
        private string QTY_LOCATION_MINIMUM = "@QTY_LOCATION_MINIMUM";
        private string QTY_PICK_INPROCESS = "@QTY_PICK_INPROCESS";
        private string QTY_REPLENISHMENT_OVERRIDE = "@QTY_REPLENISHMENT_OVERRIDE";
        private string REPLENISHMENT_CASECNT = "@REPLENISHMENT_CASECNT";
        private string REPLENISHMENT_PRIORITY = "@REPLENISHMENT_PRIORITY";
        private string REPLENISHMENT_QTY = "@REPLENISHMENT_QTY";
        private string REPLENISHMENT_SEVERITY = "@REPLENISHMENT_SEVERITY";
        private string REPLENISHMENT_UOM = "@REPLENISHMENT_UOM";
        private string SKU_ID = "@SKU_ID";
        private string SQL_CHECK_INV_SKU_LOC_ID_UNIQUE = " SELECT COUNT(1) FROM @_@INV_SKU_LOC WHERE OWNER_ID = @OWNER_ID AND SKU_ID = @SKU_ID AND LOC = @LOC ";
        private string SQL_DELETE_INV_SKU_LOC = " DELETE FROM @_@INV_SKU_LOC WHERE OWNER_ID = @OWNER_ID AND SKU_ID = @SKU_ID AND LOC = @LOC ";
        private string SQL_INSERT_INV_SKU_LOC = " INSERT INTO @_@INV_SKU_LOC ( WH_ID, OWNER_ID, SKU_ID, LOC, QTY, ALLOCATED_QTY, PICKED_QTY, EXPECTED_QTY, QTY_PICK_INPROCESS, QTY_LOCATION_LIMIT, QTY_LOCATION_MINIMUM, QTY_REPLENISHMENT_OVERRIDE, REPLENISHMENT_PRIORITY, REPLENISHMENT_SEVERITY, REPLENISHMENT_CASECNT, LOCATION_TYPE, LOCATION_UOM, REPLENISHMENT_UOM, REPLENISHMENT_QTY, TODO_USER, ALLOW_REPLENISH_FROM_CASEPICK, ALLOW_REPLENISH_FROM_BULK, ALLOW_REPLENISH_FROM_PIECEPICK, OPT_BATCHID, OPT_SEQUENCE, OPT_ADD_DEL_FLAG, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE) VALUES ( @WH_ID, @OWNER_ID, @SKU_ID, @LOC, @QTY, @ALLOCATED_QTY, @PICKED_QTY, @EXPECTED_QTY, @QTY_PICK_INPROCESS, @QTY_LOCATION_LIMIT, @QTY_LOCATION_MINIMUM, @QTY_REPLENISHMENT_OVERRIDE, @REPLENISHMENT_PRIORITY, @REPLENISHMENT_SEVERITY, @REPLENISHMENT_CASECNT, @LOCATION_TYPE, @LOCATION_UOM, @REPLENISHMENT_UOM, @REPLENISHMENT_QTY, @TODO_USER, @ALLOW_REPLENISH_FROM_CASEPICK, @ALLOW_REPLENISH_FROM_BULK, @ALLOW_REPLENISH_FROM_PIECEPICK, @OPT_BATCHID, @OPT_SEQUENCE, @OPT_ADD_DEL_FLAG, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE() )  ";
        private string SQL_SELECT_ALL_INV_SKU_LOC = "SELECT  ISL.ROWID, ISL.WH_ID, ISL.OWNER_ID, ISL.SKU_ID, ISL.LOC, ISL.QTY, ISL.ALLOCATED_QTY, ISL.PICKED_QTY, ISL.EXPECTED_QTY, ISL.QTY_PICK_INPROCESS, ISL.QTY_LOCATION_LIMIT, ISL.QTY_LOCATION_MINIMUM, ISL.QTY_REPLENISHMENT_OVERRIDE, ISL.REPLENISHMENT_PRIORITY, ISL.REPLENISHMENT_SEVERITY, ISL.REPLENISHMENT_CASECNT,  T.[TEXT] AS LOCATION_TYPE_D,ISL.LOCATION_TYPE, ISL.LOCATION_UOM, T2.[TEXT] AS REPLENISHMENT_UOM_D, ISL.REPLENISHMENT_UOM, ISL.REPLENISHMENT_QTY, ISL.TODO_USER, ISL.ALLOW_REPLENISH_FROM_CASEPICK, ISL.ALLOW_REPLENISH_FROM_BULK, ISL.ALLOW_REPLENISH_FROM_PIECEPICK, ISL.OPT_BATCHID, ISL.OPT_SEQUENCE, ISL.OPT_ADD_DEL_FLAG, ISL.TS, ISL.CREATED_BY, ISL.CREATED_DATE, ISL.UPDATED_BY, ISL.UPDATED_DATE FROM  @_@INV_SKU_LOC ISL  LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = ISL.LOCATION_TYPE AND SCD.SYS_CODE_TYPE = 'LOCUSAGE' LEFT JOIN SYSADM.TRANSLATION T ON 'LOCUSAGE.'+ISL.LOCATION_TYPE = T.ITEM_ID AND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE LEFT JOIN @_@SYS_CODE_DETAIL SCD2 ON SCD2.SYS_CODE = ISL.REPLENISHMENT_UOM AND SCD2.SYS_CODE_TYPE = 'TUOM' LEFT JOIN SYSADM.TRANSLATION T2 ON 'TUOM.'+ ISL.REPLENISHMENT_UOM = T2.ITEM_ID  AND  T2.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T2.LANG+'-'+T2.COUNTRY = @LANGUAGE";
        private string SQL_SELECT_INV_SKU_LOC = "SELECT @TOP ISL.ROWID, ISL.WH_ID, ISL.OWNER_ID, ISL.SKU_ID, ISL.LOC, ISL.QTY, ISL.ALLOCATED_QTY, ISL.PICKED_QTY, ISL.EXPECTED_QTY, ISL.QTY_PICK_INPROCESS,  ISL.QTY_LOCATION_LIMIT, ISL.QTY_LOCATION_MINIMUM, ISL.QTY_REPLENISHMENT_OVERRIDE, ISL.REPLENISHMENT_PRIORITY, ISL.REPLENISHMENT_SEVERITY, ISL.REPLENISHMENT_CASECNT,  ISL.LOCATION_TYPE, ISL.LOCATION_UOM, ISL.REPLENISHMENT_UOM, ISL.REPLENISHMENT_QTY, ISL.TODO_USER, ISL.ALLOW_REPLENISH_FROM_CASEPICK, ISL.ALLOW_REPLENISH_FROM_BULK,  ISL.ALLOW_REPLENISH_FROM_PIECEPICK, ISL.OPT_BATCHID, ISL.OPT_SEQUENCE, ISL.OPT_ADD_DEL_FLAG, ISL.TS, ISL.CREATED_BY, ISL.CREATED_DATE, ISL.UPDATED_BY, ISL.UPDATED_DATE  FROM  @_@INV_SKU_LOC ISL ";
        private string SQL_UPDATE_INV_SKU_LOC = " UPDATE @_@INV_SKU_LOC SET WH_ID = @WH_ID, QTY = @QTY, ALLOCATED_QTY = @ALLOCATED_QTY, PICKED_QTY = @PICKED_QTY, EXPECTED_QTY = @EXPECTED_QTY, QTY_PICK_INPROCESS = @QTY_PICK_INPROCESS, QTY_LOCATION_LIMIT = @QTY_LOCATION_LIMIT, QTY_LOCATION_MINIMUM = @QTY_LOCATION_MINIMUM, QTY_REPLENISHMENT_OVERRIDE = @QTY_REPLENISHMENT_OVERRIDE, REPLENISHMENT_PRIORITY = @REPLENISHMENT_PRIORITY, REPLENISHMENT_SEVERITY = @REPLENISHMENT_SEVERITY, REPLENISHMENT_CASECNT = @REPLENISHMENT_CASECNT, LOCATION_TYPE = @LOCATION_TYPE, LOCATION_UOM = @LOCATION_UOM, REPLENISHMENT_UOM = @REPLENISHMENT_UOM, REPLENISHMENT_QTY = @REPLENISHMENT_QTY, TODO_USER = @TODO_USER, ALLOW_REPLENISH_FROM_CASEPICK = @ALLOW_REPLENISH_FROM_CASEPICK, ALLOW_REPLENISH_FROM_BULK = @ALLOW_REPLENISH_FROM_BULK, ALLOW_REPLENISH_FROM_PIECEPICK = @ALLOW_REPLENISH_FROM_PIECEPICK, OPT_BATCHID = @OPT_BATCHID, OPT_SEQUENCE = @OPT_SEQUENCE, OPT_ADD_DEL_FLAG = @OPT_ADD_DEL_FLAG, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE OWNER_ID = @OWNER_ID AND SKU_ID = @SKU_ID AND LOC = @LOC  ";
        private string SQL_UPDATE_INV_SKU_LOC_ALLOCATED_QTY = " UPDATE @_@INV_SKU_LOC SET ALLOCATED_QTY = ALLOCATED_QTY + @ALLOCATED_QTY ,UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE OWNER_ID = @OWNER_ID AND SKU_ID = @SKU_ID AND LOC = @LOC ";
        private string SQL_UPDATE_INV_SKU_LOC_EXPECTED_QTY = " UPDATE @_@INV_SKU_LOC SET EXPECTED_QTY = @EXPECTED_QTY ,UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE OWNER_ID = @OWNER_ID AND SKU_ID = @SKU_ID AND LOC = @LOC ";
        private string SQL_UPDATE_INV_SKU_LOC_FROM_LOC_ALLOCATED_QTY = " UPDATE @_@INV_SKU_LOC SET ALLOCATED_QTY = ALLOCATED_QTY - @ALLOCATED_QTY, UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE OWNER_ID = @OWNER_ID AND SKU_ID = @SKU_ID AND LOC = @LOC ";
        private string SQL_UPDATE_INV_SKU_LOC_PICKED_QTY = " UPDATE @_@INV_SKU_LOC SET PICKED_QTY = PICKED_QTY + @PICKED_QTY ,ALLOCATED_QTY = ALLOCATED_QTY - @PICKED_QTY, UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE OWNER_ID = @OWNER_ID AND SKU_ID = @SKU_ID AND LOC = @LOC ";
        private string SQL_UPDATE_INV_SKU_LOC_PICKED_QTY_ONLY = " UPDATE @_@INV_SKU_LOC SET PICKED_QTY = PICKED_QTY + @PICKED_QTY ,UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE OWNER_ID = @OWNER_ID AND SKU_ID = @SKU_ID AND LOC = @LOC ";
        private string SQL_UPDATE_INV_SKU_LOC_QTY = " UPDATE @_@INV_SKU_LOC SET QTY = @QTY ,UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE OWNER_ID = @OWNER_ID AND SKU_ID = @SKU_ID AND LOC = @LOC ";
        private string SQL_UPDATE_INV_SKU_LOC_SHIPPED_QTY = " UPDATE @_@INV_SKU_LOC SET QTY = QTY - @SHIPPED_QTY, PICKED_QTY = PICKED_QTY - @SHIPPED_QTY, UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE OWNER_ID = @OWNER_ID AND SKU_ID = @SKU_ID AND LOC = @LOC ";
        private string SQL_UPDATE_INV_SKU_LOC_TO_LOC_PICKED_QTY = " UPDATE @_@INV_SKU_LOC SET PICKED_QTY = PICKED_QTY + @PICKED_QTY, UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE OWNER_ID = @OWNER_ID AND SKU_ID = @SKU_ID AND LOC = @LOC ";
        private string SQL_UPDATE_INV_SKU_LOC_UN_SHIPPED_QTY = " UPDATE @_@INV_SKU_LOC SET QTY = QTY + @SHIPPED_QTY , UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE OWNER_ID = @OWNER_ID AND SKU_ID = @SKU_ID AND LOC = @LOC ";
        private string SQL_UPDATE_INV_SKU_LOC_UN_SHIPPED_QTY_TO_PICKED = " UPDATE @_@INV_SKU_LOC SET QTY = QTY + @SHIPPED_QTY ,PICKED_QTY=PICKED_QTY+@SHIPPED_QTY, UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE OWNER_ID = @OWNER_ID AND SKU_ID = @SKU_ID AND LOC = @LOC ";
        private string SQL_UPDATE_INV_SKU_LOC_XSHIPPED_QTY = " UPDATE @_@INV_SKU_LOC SET QTY = QTY - @SHIPPED_QTY,ALLOCATED_QTY=ALLOCATED_QTY-@SHIPPED_QTY ,UPDATED_BY = @UPDATED_BY ,UPDATED_DATE = GETDATE() WHERE OWNER_ID = @OWNER_ID AND SKU_ID = @SKU_ID AND LOC = @LOC ";
        private string TODO_USER = "@TODO_USER";
        private string TS = "@TS";
        private string UPDATED_BY = "@UPDATED_BY";
        private string WH_ID = "@WH_ID";

        public bool CheckInvSkuLocIDUnique(string ownerID, string skuID, string loc, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", ownerID), new SqlParameter("@SKU_ID", skuID), new SqlParameter("@LOC", loc) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_INV_SKU_LOC_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckInvSkuLocIDUnique(DataBase dataBase, DbTransaction tran, string ownerID, string skuID, string loc, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", ownerID), new SqlParameter("@SKU_ID", skuID), new SqlParameter("@LOC", loc) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_INV_SKU_LOC_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeleteInvSkuLoc(List<string> ownerID, List<string> skuID, List<string> loc, string whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            string sqlTemp = this.SQL_DELETE_INV_SKU_LOC.Replace("@_@", whLoginID);
            try
            {
                for (int j = 0; j < ownerID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", ownerID[j]), new SqlParameter("@SKU_ID", skuID[j]), new SqlParameter("@LOC", loc[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, sqlTemp.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteInvSkuLoc(DataBase dataBase, DbTransaction tran, List<string> ownerID, List<string> skuID, List<string> loc, string whLoginID)
        {
            int result = 0;
            string sqlTemp = this.SQL_DELETE_INV_SKU_LOC.Replace("@_@", whLoginID);
            try
            {
                for (int j = 0; j < ownerID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", ownerID[j]), new SqlParameter("@SKU_ID", skuID[j]), new SqlParameter("@LOC", loc[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, sqlTemp.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public InvSkuLocInfo GetInvSkuLocByID(string ownerID, string skuID, string loc, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_INV_SKU_LOC.Replace("@_@", whLoginID) + " WHERE OWNER_ID = @OWNER_ID AND SKU_ID = @SKU_ID AND LOC = @LOC  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", ownerID), new SqlParameter("@LANGUAGE", "zh-cn"), new SqlParameter("@SKU_ID", skuID), new SqlParameter("@LOC", loc) };
            InvSkuLocInfo invSkuLocInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    invSkuLocInfo = new InvSkuLocInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["LOC"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["ALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATED_QTY"].ToString()) : 0M, (reader["PICKED_QTY"].ToString() != "") ? decimal.Parse(reader["PICKED_QTY"].ToString()) : 0M, (reader["EXPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["EXPECTED_QTY"].ToString()) : 0M, (reader["QTY_PICK_INPROCESS"].ToString() != "") ? decimal.Parse(reader["QTY_PICK_INPROCESS"].ToString()) : 0M, (reader["QTY_LOCATION_LIMIT"].ToString() != "") ? decimal.Parse(reader["QTY_LOCATION_LIMIT"].ToString()) : 0M, (reader["QTY_LOCATION_MINIMUM"].ToString() != "") ? decimal.Parse(reader["QTY_LOCATION_MINIMUM"].ToString()) : 0M, (reader["QTY_REPLENISHMENT_OVERRIDE"].ToString() != "") ? decimal.Parse(reader["QTY_REPLENISHMENT_OVERRIDE"].ToString()) : 0M, reader["REPLENISHMENT_PRIORITY"].ToString(), (reader["REPLENISHMENT_SEVERITY"].ToString() != "") ? decimal.Parse(reader["REPLENISHMENT_SEVERITY"].ToString()) : 0M, (reader["REPLENISHMENT_CASECNT"].ToString() != "") ? decimal.Parse(reader["REPLENISHMENT_CASECNT"].ToString()) : 0M, reader["LOCATION_TYPE"].ToString(), reader["LOCATION_UOM"].ToString(), reader["REPLENISHMENT_UOM"].ToString(), (reader["REPLENISHMENT_QTY"].ToString() != "") ? decimal.Parse(reader["REPLENISHMENT_QTY"].ToString()) : 0M, reader["TODO_USER"].ToString(), (reader["ALLOW_REPLENISH_FROM_CASEPICK"].ToString() != "") ? int.Parse(reader["ALLOW_REPLENISH_FROM_CASEPICK"].ToString()) : 0, (reader["ALLOW_REPLENISH_FROM_BULK"].ToString() != "") ? int.Parse(reader["ALLOW_REPLENISH_FROM_BULK"].ToString()) : 0, (reader["ALLOW_REPLENISH_FROM_PIECEPICK"].ToString() != "") ? int.Parse(reader["ALLOW_REPLENISH_FROM_PIECEPICK"].ToString()) : 0, reader["OPT_BATCHID"].ToString(), reader["OPT_SEQUENCE"].ToString(), reader["OPT_ADD_DEL_FLAG"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), reader["REPLENISHMENT_UOM_D"].ToString(), reader["LOCATION_TYPE_D"].ToString());
                }
            }
            return invSkuLocInfo;
        }

        public List<InvSkuLocInfo> GetInvSkuLocByID(string ownerID, string skuID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_INV_SKU_LOC.Replace("@_@", whLoginID) + " WHERE ISL.OWNER_ID = @OWNER_ID AND ISL.SKU_ID = @SKU_ID";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", ownerID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@SKU_ID", skuID) };
            List<InvSkuLocInfo> invSkuLocInfoList = new List<InvSkuLocInfo>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    InvSkuLocInfo invSkuLocInfo = new InvSkuLocInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["LOC"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["ALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATED_QTY"].ToString()) : 0M, (reader["PICKED_QTY"].ToString() != "") ? decimal.Parse(reader["PICKED_QTY"].ToString()) : 0M, (reader["EXPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["EXPECTED_QTY"].ToString()) : 0M, (reader["QTY_PICK_INPROCESS"].ToString() != "") ? decimal.Parse(reader["QTY_PICK_INPROCESS"].ToString()) : 0M, (reader["QTY_LOCATION_LIMIT"].ToString() != "") ? decimal.Parse(reader["QTY_LOCATION_LIMIT"].ToString()) : 0M, (reader["QTY_LOCATION_MINIMUM"].ToString() != "") ? decimal.Parse(reader["QTY_LOCATION_MINIMUM"].ToString()) : 0M, (reader["QTY_REPLENISHMENT_OVERRIDE"].ToString() != "") ? decimal.Parse(reader["QTY_REPLENISHMENT_OVERRIDE"].ToString()) : 0M, reader["REPLENISHMENT_PRIORITY"].ToString(), (reader["REPLENISHMENT_SEVERITY"].ToString() != "") ? decimal.Parse(reader["REPLENISHMENT_SEVERITY"].ToString()) : 0M, (reader["REPLENISHMENT_CASECNT"].ToString() != "") ? decimal.Parse(reader["REPLENISHMENT_CASECNT"].ToString()) : 0M, reader["LOCATION_TYPE"].ToString(), reader["LOCATION_UOM"].ToString(), reader["REPLENISHMENT_UOM"].ToString(), (reader["REPLENISHMENT_QTY"].ToString() != "") ? decimal.Parse(reader["REPLENISHMENT_QTY"].ToString()) : 0M, reader["TODO_USER"].ToString(), (reader["ALLOW_REPLENISH_FROM_CASEPICK"].ToString() != "") ? int.Parse(reader["ALLOW_REPLENISH_FROM_CASEPICK"].ToString()) : 0, (reader["ALLOW_REPLENISH_FROM_BULK"].ToString() != "") ? int.Parse(reader["ALLOW_REPLENISH_FROM_BULK"].ToString()) : 0, (reader["ALLOW_REPLENISH_FROM_PIECEPICK"].ToString() != "") ? int.Parse(reader["ALLOW_REPLENISH_FROM_PIECEPICK"].ToString()) : 0, reader["OPT_BATCHID"].ToString(), reader["OPT_SEQUENCE"].ToString(), reader["OPT_ADD_DEL_FLAG"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["REPLENISHMENT_UOM_D"].ToString(), reader["LOCATION_TYPE_D"].ToString());
                    invSkuLocInfoList.Add(invSkuLocInfo);
                }
            }
            return invSkuLocInfoList;
        }

        public InvSkuLocInfo GetInvSkuLocByID(DataBase dataBase, DbTransaction tran, string ownerID, string skuID, string loc, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_INV_SKU_LOC.Replace("@_@", whLoginID) + " WHERE OWNER_ID = @OWNER_ID AND SKU_ID = @SKU_ID AND LOC = @LOC  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", ownerID), new SqlParameter("@SKU_ID", skuID), new SqlParameter("@LANGUAGE", "zh-cn"), new SqlParameter("@LOC", loc) };
            InvSkuLocInfo invSkuLocInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                invSkuLocInfo = new InvSkuLocInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["LOC"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["ALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATED_QTY"].ToString()) : 0M, (reader["PICKED_QTY"].ToString() != "") ? decimal.Parse(reader["PICKED_QTY"].ToString()) : 0M, (reader["EXPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["EXPECTED_QTY"].ToString()) : 0M, (reader["QTY_PICK_INPROCESS"].ToString() != "") ? decimal.Parse(reader["QTY_PICK_INPROCESS"].ToString()) : 0M, (reader["QTY_LOCATION_LIMIT"].ToString() != "") ? decimal.Parse(reader["QTY_LOCATION_LIMIT"].ToString()) : 0M, (reader["QTY_LOCATION_MINIMUM"].ToString() != "") ? decimal.Parse(reader["QTY_LOCATION_MINIMUM"].ToString()) : 0M, (reader["QTY_REPLENISHMENT_OVERRIDE"].ToString() != "") ? decimal.Parse(reader["QTY_REPLENISHMENT_OVERRIDE"].ToString()) : 0M, reader["REPLENISHMENT_PRIORITY"].ToString(), (reader["REPLENISHMENT_SEVERITY"].ToString() != "") ? decimal.Parse(reader["REPLENISHMENT_SEVERITY"].ToString()) : 0M, (reader["REPLENISHMENT_CASECNT"].ToString() != "") ? decimal.Parse(reader["REPLENISHMENT_CASECNT"].ToString()) : 0M, reader["LOCATION_TYPE"].ToString(), reader["LOCATION_UOM"].ToString(), reader["REPLENISHMENT_UOM"].ToString(), (reader["REPLENISHMENT_QTY"].ToString() != "") ? decimal.Parse(reader["REPLENISHMENT_QTY"].ToString()) : 0M, reader["TODO_USER"].ToString(), (reader["ALLOW_REPLENISH_FROM_CASEPICK"].ToString() != "") ? int.Parse(reader["ALLOW_REPLENISH_FROM_CASEPICK"].ToString()) : 0, (reader["ALLOW_REPLENISH_FROM_BULK"].ToString() != "") ? int.Parse(reader["ALLOW_REPLENISH_FROM_BULK"].ToString()) : 0, (reader["ALLOW_REPLENISH_FROM_PIECEPICK"].ToString() != "") ? int.Parse(reader["ALLOW_REPLENISH_FROM_PIECEPICK"].ToString()) : 0, reader["OPT_BATCHID"].ToString(), reader["OPT_SEQUENCE"].ToString(), reader["OPT_ADD_DEL_FLAG"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), reader["REPLENISHMENT_UOM_D"].ToString(), reader["LOCATION_TYPE_D"].ToString());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return invSkuLocInfo;
        }

        public List<InvSkuLocInfo> GetInvSkuLocByLoc(string loc, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_INV_SKU_LOC.Replace("@_@", whLoginID) + " WHERE  LOC = @LOC  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LOC", loc), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage) };
            List<InvSkuLocInfo> invSkuLocInfoList = new List<InvSkuLocInfo>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    InvSkuLocInfo invSkuLocInfo = new InvSkuLocInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["LOC"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["ALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATED_QTY"].ToString()) : 0M, (reader["PICKED_QTY"].ToString() != "") ? decimal.Parse(reader["PICKED_QTY"].ToString()) : 0M, (reader["EXPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["EXPECTED_QTY"].ToString()) : 0M, (reader["QTY_PICK_INPROCESS"].ToString() != "") ? decimal.Parse(reader["QTY_PICK_INPROCESS"].ToString()) : 0M, (reader["QTY_LOCATION_LIMIT"].ToString() != "") ? decimal.Parse(reader["QTY_LOCATION_LIMIT"].ToString()) : 0M, (reader["QTY_LOCATION_MINIMUM"].ToString() != "") ? decimal.Parse(reader["QTY_LOCATION_MINIMUM"].ToString()) : 0M, (reader["QTY_REPLENISHMENT_OVERRIDE"].ToString() != "") ? decimal.Parse(reader["QTY_REPLENISHMENT_OVERRIDE"].ToString()) : 0M, reader["REPLENISHMENT_PRIORITY"].ToString(), (reader["REPLENISHMENT_SEVERITY"].ToString() != "") ? decimal.Parse(reader["REPLENISHMENT_SEVERITY"].ToString()) : 0M, (reader["REPLENISHMENT_CASECNT"].ToString() != "") ? decimal.Parse(reader["REPLENISHMENT_CASECNT"].ToString()) : 0M, reader["LOCATION_TYPE"].ToString(), reader["LOCATION_UOM"].ToString(), reader["REPLENISHMENT_UOM"].ToString(), (reader["REPLENISHMENT_QTY"].ToString() != "") ? decimal.Parse(reader["REPLENISHMENT_QTY"].ToString()) : 0M, reader["TODO_USER"].ToString(), (reader["ALLOW_REPLENISH_FROM_CASEPICK"].ToString() != "") ? int.Parse(reader["ALLOW_REPLENISH_FROM_CASEPICK"].ToString()) : 0, (reader["ALLOW_REPLENISH_FROM_BULK"].ToString() != "") ? int.Parse(reader["ALLOW_REPLENISH_FROM_BULK"].ToString()) : 0, (reader["ALLOW_REPLENISH_FROM_PIECEPICK"].ToString() != "") ? int.Parse(reader["ALLOW_REPLENISH_FROM_PIECEPICK"].ToString()) : 0, reader["OPT_BATCHID"].ToString(), reader["OPT_SEQUENCE"].ToString(), reader["OPT_ADD_DEL_FLAG"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["REPLENISHMENT_UOM_D"].ToString(), reader["LOCATION_TYPE_D"].ToString());
                    invSkuLocInfoList.Add(invSkuLocInfo);
                }
            }
            return invSkuLocInfoList;
        }

        public List<InvSkuLocInfo> GetInvSkuLocBySqlWhere(List<string> sqlWhere, string strTop, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_INV_SKU_LOC + " WHERE (ISL.EXPECTED_QTY >0  OR ISL.QTY-ISL.PICKED_QTY< ISL.QTY_LOCATION_MINIMUM)";
            if (!string.IsNullOrEmpty(strTop))
            {
                sql = sql.Replace("@TOP", " TOP " + strTop);
            }
            else
            {
                sql = sql.Replace("@TOP", "");
            }
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                sql = sql + " AND " + sqlWhere[i].ToString();
            }
            List<InvSkuLocInfo> invSkuLocInfoList = new List<InvSkuLocInfo>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql.Replace("@_@", whLoginID)))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    InvSkuLocInfo invSkuLocInfo = new InvSkuLocInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["LOC"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["ALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATED_QTY"].ToString()) : 0M, (reader["PICKED_QTY"].ToString() != "") ? decimal.Parse(reader["PICKED_QTY"].ToString()) : 0M, (reader["EXPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["EXPECTED_QTY"].ToString()) : 0M, (reader["QTY_PICK_INPROCESS"].ToString() != "") ? decimal.Parse(reader["QTY_PICK_INPROCESS"].ToString()) : 0M, (reader["QTY_LOCATION_LIMIT"].ToString() != "") ? decimal.Parse(reader["QTY_LOCATION_LIMIT"].ToString()) : 0M, (reader["QTY_LOCATION_MINIMUM"].ToString() != "") ? decimal.Parse(reader["QTY_LOCATION_MINIMUM"].ToString()) : 0M, (reader["QTY_REPLENISHMENT_OVERRIDE"].ToString() != "") ? decimal.Parse(reader["QTY_REPLENISHMENT_OVERRIDE"].ToString()) : 0M, reader["REPLENISHMENT_PRIORITY"].ToString(), (reader["REPLENISHMENT_SEVERITY"].ToString() != "") ? decimal.Parse(reader["REPLENISHMENT_SEVERITY"].ToString()) : 0M, (reader["REPLENISHMENT_CASECNT"].ToString() != "") ? decimal.Parse(reader["REPLENISHMENT_CASECNT"].ToString()) : 0M, reader["LOCATION_TYPE"].ToString(), reader["LOCATION_UOM"].ToString(), reader["REPLENISHMENT_UOM"].ToString(), (reader["REPLENISHMENT_QTY"].ToString() != "") ? decimal.Parse(reader["REPLENISHMENT_QTY"].ToString()) : 0M, reader["TODO_USER"].ToString(), (reader["ALLOW_REPLENISH_FROM_CASEPICK"].ToString() != "") ? int.Parse(reader["ALLOW_REPLENISH_FROM_CASEPICK"].ToString()) : 0, (reader["ALLOW_REPLENISH_FROM_BULK"].ToString() != "") ? int.Parse(reader["ALLOW_REPLENISH_FROM_BULK"].ToString()) : 0, (reader["ALLOW_REPLENISH_FROM_PIECEPICK"].ToString() != "") ? int.Parse(reader["ALLOW_REPLENISH_FROM_PIECEPICK"].ToString()) : 0, reader["OPT_BATCHID"].ToString(), reader["OPT_SEQUENCE"].ToString(), reader["OPT_ADD_DEL_FLAG"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), "", "");
                    invSkuLocInfoList.Add(invSkuLocInfo);
                }
            }
            return invSkuLocInfoList;
        }

        public int InsertInvSkuLoc(InvSkuLocInfo invSkuLocInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                invSkuLocInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_InvSkuLoc_Parameters(invSkuLocInfo);
                if (paras != null)
                {
                    result += DBHelper.ExecuteNonQuery(CommandType.Text, this.SQL_INSERT_INV_SKU_LOC.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int InsertInvSkuLoc(DataBase dataBase, DbTransaction tran, InvSkuLocInfo invSkuLocInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                invSkuLocInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_InvSkuLoc_Parameters(invSkuLocInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_INV_SKU_LOC.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_InvSkuLoc_Parameters(InvSkuLocInfo invSkuLocInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.OWNER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.SKU_ID, SqlDbType.VarChar, 50), new SqlParameter(this.LOC, SqlDbType.VarChar, 20), new SqlParameter(this.QTY, SqlDbType.Decimal, 13), new SqlParameter(this.ALLOCATED_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.PICKED_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.EXPECTED_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.QTY_PICK_INPROCESS, SqlDbType.Decimal, 13), new SqlParameter(this.QTY_LOCATION_LIMIT, SqlDbType.Decimal, 13), new SqlParameter(this.QTY_LOCATION_MINIMUM, SqlDbType.Decimal, 13), new SqlParameter(this.QTY_REPLENISHMENT_OVERRIDE, SqlDbType.Decimal, 13), new SqlParameter(this.REPLENISHMENT_PRIORITY, SqlDbType.VarChar, 20), new SqlParameter(this.REPLENISHMENT_SEVERITY, SqlDbType.Decimal, 13), new SqlParameter(this.REPLENISHMENT_CASECNT, SqlDbType.Decimal, 13), new SqlParameter(this.LOCATION_TYPE, SqlDbType.VarChar, 20), 
                new SqlParameter(this.LOCATION_UOM, SqlDbType.VarChar, 20), new SqlParameter(this.REPLENISHMENT_UOM, SqlDbType.VarChar, 20), new SqlParameter(this.REPLENISHMENT_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.TODO_USER, SqlDbType.VarChar, 20), new SqlParameter(this.ALLOW_REPLENISH_FROM_CASEPICK, SqlDbType.Int, 4), new SqlParameter(this.ALLOW_REPLENISH_FROM_BULK, SqlDbType.Int, 4), new SqlParameter(this.ALLOW_REPLENISH_FROM_PIECEPICK, SqlDbType.Int, 4), new SqlParameter(this.OPT_BATCHID, SqlDbType.VarChar, 20), new SqlParameter(this.OPT_SEQUENCE, SqlDbType.VarChar, 20), new SqlParameter(this.OPT_ADD_DEL_FLAG, SqlDbType.VarChar, 1), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20)
             };
            if (!string.IsNullOrEmpty(invSkuLocInfo.WhID))
            {
                paramArray[0].Value = invSkuLocInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = invSkuLocInfo.OwnerID;
            paramArray[2].Value = invSkuLocInfo.SkuID;
            paramArray[3].Value = invSkuLocInfo.Loc;
            paramArray[4].Value = invSkuLocInfo.Qty;
            paramArray[5].Value = invSkuLocInfo.AllocatedQty;
            paramArray[6].Value = invSkuLocInfo.PickedQty;
            paramArray[7].Value = invSkuLocInfo.ExpectedQty;
            paramArray[8].Value = invSkuLocInfo.QtyPickInprocess;
            paramArray[9].Value = invSkuLocInfo.QtyLocationLimit;
            paramArray[10].Value = invSkuLocInfo.QtyLocationMinimum;
            paramArray[11].Value = invSkuLocInfo.QtyReplenishmentOverride;
            paramArray[12].Value = string.IsNullOrEmpty(invSkuLocInfo.ReplenishmentPriority) ? "" : invSkuLocInfo.ReplenishmentPriority;
            paramArray[13].Value = invSkuLocInfo.ReplenishmentSeverity;
            paramArray[14].Value = invSkuLocInfo.ReplenishmentCasecnt;
            paramArray[15].Value = string.IsNullOrEmpty(invSkuLocInfo.LocationType) ? "" : invSkuLocInfo.LocationType;
            if (!string.IsNullOrEmpty(invSkuLocInfo.LocationUom))
            {
                paramArray[0x10].Value = invSkuLocInfo.LocationUom;
            }
            else
            {
                paramArray[0x10].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(invSkuLocInfo.ReplenishmentUom))
            {
                paramArray[0x11].Value = invSkuLocInfo.ReplenishmentUom;
            }
            else
            {
                paramArray[0x11].Value = DBNull.Value;
            }
            paramArray[0x12].Value = invSkuLocInfo.ReplenishmentQty;
            if (!string.IsNullOrEmpty(invSkuLocInfo.TodoUser))
            {
                paramArray[0x13].Value = invSkuLocInfo.TodoUser;
            }
            else
            {
                paramArray[0x13].Value = DBNull.Value;
            }
            paramArray[20].Value = invSkuLocInfo.AllowReplenishFromCasepick;
            paramArray[0x15].Value = invSkuLocInfo.AllowReplenishFromBulk;
            paramArray[0x16].Value = invSkuLocInfo.AllowReplenishFromPiecepick;
            if (!string.IsNullOrEmpty(invSkuLocInfo.OptBatchid))
            {
                paramArray[0x17].Value = invSkuLocInfo.OptBatchid;
            }
            else
            {
                paramArray[0x17].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(invSkuLocInfo.OptSequence))
            {
                paramArray[0x18].Value = invSkuLocInfo.OptSequence;
            }
            else
            {
                paramArray[0x18].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(invSkuLocInfo.OptAddDelFlag))
            {
                paramArray[0x19].Value = invSkuLocInfo.OptAddDelFlag;
            }
            else
            {
                paramArray[0x19].Value = DBNull.Value;
            }
            paramArray[0x1a].Value = invSkuLocInfo.CreatedBy;
            paramArray[0x1b].Value = invSkuLocInfo.UpdatedBy;
            return paramArray;
        }

        public int UpdateInvSkuLoc(InvSkuLocInfo invSkuLocInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                invSkuLocInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_InvSkuLoc_Parameters(invSkuLocInfo);
                if (paras != null)
                {
                    result += DBHelper.ExecuteNonQuery(CommandType.Text, this.SQL_UPDATE_INV_SKU_LOC.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateInvSkuLoc(DataBase dataBase, DbTransaction tran, InvSkuLocInfo invSkuLocInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                invSkuLocInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_InvSkuLoc_Parameters(invSkuLocInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_INV_SKU_LOC.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateInvSkuLoc(DataBase dataBase, DbTransaction tran, InvSkuLocInfo invSkuLocInfo, string updateType, string whLoginID)
        {
            int result = 0;
            try
            {
                string sql = string.Empty;
                SqlParameter[] paras = null;
                switch (updateType)
                {
                    case "Qty":
                        sql = this.SQL_UPDATE_INV_SKU_LOC_QTY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@QTY", invSkuLocInfo.Qty), new SqlParameter("@UPDATED_BY", invSkuLocInfo.UpdatedBy), new SqlParameter("@OWNER_ID", invSkuLocInfo.OwnerID), new SqlParameter("@SKU_ID", invSkuLocInfo.SkuID), new SqlParameter("@LOC", invSkuLocInfo.Loc) };
                        break;

                    case "ExpectedQty":
                        sql = this.SQL_UPDATE_INV_SKU_LOC_EXPECTED_QTY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@EXPECTED_QTY", invSkuLocInfo.ExpectedQty), new SqlParameter("@UPDATED_BY", invSkuLocInfo.UpdatedBy), new SqlParameter("@OWNER_ID", invSkuLocInfo.OwnerID), new SqlParameter("@SKU_ID", invSkuLocInfo.SkuID), new SqlParameter("@LOC", invSkuLocInfo.Loc) };
                        break;

                    case "AllocatedQty":
                        sql = this.SQL_UPDATE_INV_SKU_LOC_ALLOCATED_QTY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@ALLOCATED_QTY", invSkuLocInfo.AllocatedQty), new SqlParameter("@UPDATED_BY", invSkuLocInfo.UpdatedBy), new SqlParameter("@OWNER_ID", invSkuLocInfo.OwnerID), new SqlParameter("@SKU_ID", invSkuLocInfo.SkuID), new SqlParameter("@LOC", invSkuLocInfo.Loc) };
                        break;

                    case "FromLocAllocatedQty":
                        sql = this.SQL_UPDATE_INV_SKU_LOC_FROM_LOC_ALLOCATED_QTY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@ALLOCATED_QTY", invSkuLocInfo.AllocatedQty), new SqlParameter("@UPDATED_BY", invSkuLocInfo.UpdatedBy), new SqlParameter("@OWNER_ID", invSkuLocInfo.OwnerID), new SqlParameter("@SKU_ID", invSkuLocInfo.SkuID), new SqlParameter("@LOC", invSkuLocInfo.Loc) };
                        break;

                    case "PickedQty":
                        sql = this.SQL_UPDATE_INV_SKU_LOC_PICKED_QTY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@PICKED_QTY", invSkuLocInfo.PickedQty), new SqlParameter("@UPDATED_BY", invSkuLocInfo.UpdatedBy), new SqlParameter("@OWNER_ID", invSkuLocInfo.OwnerID), new SqlParameter("@SKU_ID", invSkuLocInfo.SkuID), new SqlParameter("@LOC", invSkuLocInfo.Loc) };
                        break;

                    case "ToLocPickedQty":
                        sql = this.SQL_UPDATE_INV_SKU_LOC_TO_LOC_PICKED_QTY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@PICKED_QTY", invSkuLocInfo.PickedQty), new SqlParameter("@UPDATED_BY", invSkuLocInfo.UpdatedBy), new SqlParameter("@OWNER_ID", invSkuLocInfo.OwnerID), new SqlParameter("@SKU_ID", invSkuLocInfo.SkuID), new SqlParameter("@LOC", invSkuLocInfo.Loc) };
                        break;

                    case "ShippedQty":
                        sql = this.SQL_UPDATE_INV_SKU_LOC_SHIPPED_QTY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@SHIPPED_QTY", invSkuLocInfo.Qty), new SqlParameter("@UPDATED_BY", invSkuLocInfo.UpdatedBy), new SqlParameter("@OWNER_ID", invSkuLocInfo.OwnerID), new SqlParameter("@SKU_ID", invSkuLocInfo.SkuID), new SqlParameter("@LOC", invSkuLocInfo.Loc) };
                        break;

                    case "PickedQtyOnly":
                        sql = this.SQL_UPDATE_INV_SKU_LOC_PICKED_QTY_ONLY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@PICKED_QTY", invSkuLocInfo.PickedQty), new SqlParameter("@UPDATED_BY", invSkuLocInfo.UpdatedBy), new SqlParameter("@OWNER_ID", invSkuLocInfo.OwnerID), new SqlParameter("@SKU_ID", invSkuLocInfo.SkuID), new SqlParameter("@LOC", invSkuLocInfo.Loc) };
                        break;

                    case "UnShippedQty":
                        sql = this.SQL_UPDATE_INV_SKU_LOC_UN_SHIPPED_QTY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@SHIPPED_QTY", invSkuLocInfo.Qty), new SqlParameter("@UPDATED_BY", invSkuLocInfo.UpdatedBy), new SqlParameter("@OWNER_ID", invSkuLocInfo.OwnerID), new SqlParameter("@SKU_ID", invSkuLocInfo.SkuID), new SqlParameter("@LOC", invSkuLocInfo.Loc) };
                        break;

                    case "UnShippedQtyToPicked":
                        sql = this.SQL_UPDATE_INV_SKU_LOC_UN_SHIPPED_QTY_TO_PICKED.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@SHIPPED_QTY", invSkuLocInfo.Qty), new SqlParameter("@UPDATED_BY", invSkuLocInfo.UpdatedBy), new SqlParameter("@OWNER_ID", invSkuLocInfo.OwnerID), new SqlParameter("@SKU_ID", invSkuLocInfo.SkuID), new SqlParameter("@LOC", invSkuLocInfo.Loc) };
                        break;

                    case "XShippedQty":
                        sql = this.SQL_UPDATE_INV_SKU_LOC_XSHIPPED_QTY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@SHIPPED_QTY", invSkuLocInfo.Qty), new SqlParameter("@UPDATED_BY", invSkuLocInfo.UpdatedBy), new SqlParameter("@OWNER_ID", invSkuLocInfo.OwnerID), new SqlParameter("@SKU_ID", invSkuLocInfo.SkuID), new SqlParameter("@LOC", invSkuLocInfo.Loc) };
                        break;
                }
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, sql, paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

